<?php
/**
 * db 数据库存处理类
 * @final 
 * @category	alps
 * @package 	alps
 * @author km3945 <km3945@gmail.com>
 * @copyright Copyright (c) 2005-2006 km3945.  All rights reserved. 
 */

final class core_mysql extends PDO
{
	/**
	 * 数据库配置参数
	 * @access pirvate
	 * @var array|null
	 */
	private $DsnConfig = null;
	
	/**
	 * 分页查询的SQL语句
	 * @access pirvate
	 * @var array|null
	 */
	private $pageSql = null;
	
	/**
	 * 分页:当前的分页位置
	 * @access private
	 * @var int
	 */
	Private $m_iNowPages = 0;

	/**
	 * 分页:每页大小
	 * @access private
	 * @var int
	 */
	Private $m_iPageSize = 0;

	/**
	 * 分页:总记录数
	 * @access private
	 * @var int
	 */
	Private $m_iRsCounts = 0;

	/**
	 * 分页:总分页数
	 * @access private
	 * @var int
	 */
	Private $m_iPageCount= 0;

	/**
	 * 构造
	 * @access public
	 * @param array $DSNconfig 数据库配置参数
	 * @throws PDOException
	 * @return void
	 */
	public function __construct( $DSNconfig = null)
	{
		if ( !is_null( $DSNconfig))
		{
			$this->setDsnConfig( $DSNconfig);
		}
		else 
		{
			$this->setDsnConfig( alps::getCFG( 'DBA'));
		}

		parent::__construct( $this->DsnConfig['DSN'], $this->DsnConfig['USER'], $this->DsnConfig['PASS'], $this->DsnConfig['OPTIONS']);
		parent::setAttribute(parent::ATTR_ERRMODE, parent::ERRMODE_EXCEPTION);
		parent::setAttribute(parent::ATTR_AUTOCOMMIT, true);
		parent::setAttribute(parent::ATTR_CASE, parent::CASE_NATURAL);
		parent::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
		parse_str(str_replace(';','&',$this->DsnConfig['DSN']), $out);
		parent::exec(sprintf('SET NAMES \'%s\'', $out['charset']));
		parent::exec('SET sql_mode=\'\'');
	}

	/**
	 * 设置数据库DSN配置参数
	 * @access public
	 * @param array	$config 数据库DSN配置参数数组
	 * @throws PDOException
	 * @return void
	 */
	public function setDsnConfig( $config)
	{
		if ( !is_array( $config) || empty( $config))
		{
			throw new PDOException( 'Error：$config 必须是数组并不能为空.');
		}
		
		$this->DsnConfig = $config;
	}

	/**
	 * 返回数据库DSN配置参数
	 * @access public
	 * @return array
	 */
	public function getDsnConfig()
	{
		return $this->DsnConfig;
	}
	
	/**
	 * 获得一条数据
	 * @access public
	 * @param string $sSql
	 * @return array|null
	 */
	public function getOne( $sSql)
	{
		$sth = $this->prepare( $sSql);
		$sth->execute();
		$rs =$sth->fetch( PDO::FETCH_ASSOC);
		return $rs ? $rs : null;
	}
	
	/**
	 * 执行分页查询
	 * @access 	public
	 * @param 	string 	$sPageSql 		SQL语句
	 * @param 	int 	$iPageSize 		分页大小
	 * @param 	string 	$sCountField 	统计字段|null
	 * @param 	string 	$sCountSql 		统计SQL|null
	 * @return 	array|null
	 */
	public function pageQuery($sPageSql, $iPageSize, $sCountField= null, $sCountSql=null)
	{
		
		$s_page_sql	= trim($sPageSql);
		$i_page_size= intval($iPageSize);
		 $s_count_sql= ( !is_null( $sCountSql)) ? trim($sCountSql) : preg_replace(array("/^select\s(.*)\sfrom\s/i", "/\sorder\sby(.*)/i"), array('SELECT COUNT('.(is_null($sCountField)?'*':$sCountField).') FROM ', ''), $s_page_sql);
	 
		$rs = $this->prepare( $s_count_sql);
		$rs->execute();
		   $i_total = (int)$rs->fetchColumn();
		if($i_total > 0)
		{
			$i_ps = $_GET[SYS_PAGE_TAG];
			$i_ps = intval($i_ps);
			$this->m_iNowPages = ($i_ps<1) ? 1 : $i_ps;
			$i_page_size =($i_page_size<1) ? 10 : $i_page_size;
			$this->m_iPageSize = intval($iPageSize);
			$this->m_iRsCounts = $i_total;
			$this->m_iPageCount= ceil($this->m_iRsCounts / $this->m_iPageSize);
			$this->m_iNowPages = ($this->m_iNowPages >$this->m_iPageCount) ? $this->m_iPageCount : $this->m_iNowPages;
			$i_offset = $this->m_iNowPages == 1 ? 0 : ($this->m_iNowPages - 1) * $this->m_iPageSize;
			$i_residue= $this->m_iRsCounts % $this->m_iPageSize;
			$i_offset_size = $i_residue >0 ? $this->m_iNowPages == $this->m_iPageCount ? $i_residue : $this->m_iPageSize : $this->m_iPageSize;
			$s_page_sql .= ' LIMIT ' . $i_offset . ', ' . $i_offset_size;
			$this->pageSql = array('countSQL' 	=> $s_count_sql,'pageSQL'	=> $s_page_sql);
			$sth = $this->prepare( $s_page_sql);
			$sth->execute();
			return $sth->fetchAll( PDO::FETCH_ASSOC);
		}
		else
		{
			$this->pageSql = array('countSQL' 	=> $s_count_sql,'pageSQL'	=> null);
			return null;
		}
	}
	
	/**
	 * 返回分页查询的2个SQL语句(数组)
	 * @access 	public
	 * @return	array|null
	 */	
	public function getPageSql()
	{
		return $this->pageSql;
	}

	/**
	 * 返回分页数据信息(数组)
	 * @access 	public
	 * @return	mixed array|int
	 */	
	public function getPageInfo($index=null)
	{
		$rs = array(
			'nowPages'	=>	$this->m_iNowPages,
			'pageSize'	=>	$this->m_iPageSize,
			'rsCounts'	=>	$this->m_iRsCounts,
			'pageCount'	=>	$this->m_iPageCount
		);
		
		if (!is_null($index))
			return $rs[$index];
			
		return $rs;
	}
	
	/**
	 * 获得分页工具条
	 * @access 	public
	 * @param 	int 	$style 		式样|0
	 * @param 	boolean $isSearch 	是否要搜索条|false
	 * @param 	int 	$iPnum 		跳转数|5
	 * @return 	string|null
	 */
	public function getPageToolbar($style=0, $isSearch=false, $iPnum=5)
	{
		if(!empty($this->m_iNowPages) && !empty($this->m_iRsCounts))
		{
			$htmSearch  = $multipage = '';
 			$key 		= trim($_GET[SYS_KEY_TAG]);
			$noPsUrl 	= alps::fontmatQueryString(array(SYS_PAGE_TAG));
			$phpself    = $_SERVER['PHP_SELF'];
			//$noPsUrl 	= empty($noPsUrl) ? '/?'.SYS_PAGE_TAG.'=' : '/?'.$noPsUrl.'&'.SYS_PAGE_TAG.'=';
            $noPsUrl 	= empty($noPsUrl) ? '?'.SYS_PAGE_TAG.'=' : '?'.$noPsUrl.'&'.SYS_PAGE_TAG.'=';
            $noPsUrl    = $phpself.$noPsUrl;
	//print_r($noPsUrl);
			$noPsKeyUrl1= alps::fontmatQueryString(array(SYS_PAGE_TAG, SYS_KEY_TAG));
			$noPsKeyUrl2= empty($noPsKeyUrl1) ? '/?'.SYS_KEY_TAG.'=' : '/?'.$noPsKeyUrl1.'&'.SYS_KEY_TAG.'=';
			$htmTotals 	= (!empty($key) ? '<span>&nbsp;搜索' : '<span>&nbsp;') . '分页：' . $this->m_iNowPages . '/' . $this->m_iPageCount . '页&nbsp;</span>';
			$htmFirst	= $this->m_iNowPages > 1 ? '<a href="'. $noPsUrl . 1 .'" title="首页" class="cr">&nbsp;首页</a><a href="'. $noPsUrl . ($this->m_iNowPages - 1) .'" title="上页" class="c2">&nbsp;上页&nbsp;</a>' : '<a class="cr cc9">&nbsp;首页</a><a class="c2 cc9">&nbsp;上页&nbsp;</a>';
			$htmLast	= $this->m_iNowPages < $this->m_iPageCount ? '<a href="'. $noPsUrl . ($this->m_iNowPages + 1) .'" title="下页" class="c2">&nbsp;下页&nbsp;</a><a href="'. $noPsUrl . $this->m_iPageCount .'" title="末页" class="cl">末页&nbsp;</a>' : '<a class="c2 cc9">&nbsp;下页&nbsp;</a><a class="cl cc9">末页&nbsp;</a>';			

			$offset = 2;
			if( $iPnum > $this->m_iPageCount) 
			{
				$from = 1;
				$to = $this->m_iPageCount;
			} 
			else 
			{
				$from = $this->m_iNowPages - $offset;
				$to = $from + $iPnum - 1;
				if( $from < 1) 
				{
					$to = $this->m_iNowPages + 1 - $from;
					$from = 1;
					if($to - $from < $iPnum) 
					{
						$to = $iPnum;
					}
				} 
				elseif($to > $this->m_iPageCount) 
				{
					$from = $this->m_iPageCount - $iPnum + 1;
					$to = $this->m_iPageCount;
				}
			}
		
			$multipage .= $this->m_iNowPages > 1 && $this->m_iPageCount > $iPnum ? '<a href="'.$noPsUrl.'1" class="jt jt_le1" title="首页">&nbsp;</a><a href="'.$noPsUrl.($this->m_iNowPages - 1).'" class="jt jt_ln1" title="上一页">&nbsp;</a>' : '<a class="jt jt_le0">&nbsp;</a><a class="jt jt_ln0">&nbsp;</a>';
			for($i = $from; $i <= $to; $i++) 
			{
				$multipage .= $i == $this->m_iNowPages ? '<a class="p_curpage"title="第'. $i .'页">&nbsp;'.$i.'&nbsp;</a>' : '<a href="'.$noPsUrl.$i.'" title="第'. $i .'页">&nbsp;'.$i.'&nbsp;</a>';
			}
			$multipage .= $this->m_iNowPages < $this->m_iPageCount ? '<a href="'.$noPsUrl.($this->m_iNowPages + 1).'" class="jt jt_rn1" title="下一页">&nbsp;</a><a href="'.$noPsUrl.$this->m_iPageCount.'" class="jt jt_re1" title="末页">&nbsp;</a>':'<a class="jt jt_rn0">&nbsp;</a><a class="jt jt_re0">&nbsp;</a>';
			
			$inputLen =	strlen($this->m_iPageCount);
			if ($isSearch)
			{
				$htmSearch  = '<script language="javascript" type="text/JavaScript">function __o_SearchPost_o__(){var o=document.getElementById(\'_o_K_o_\');if(o.value==\'\'){window.location.href=\'/?' . $noPsKeyUrl1 .'\';}else{window.location.href=\''. $noPsKeyUrl2. '\'+o.value;}}</script>';
				$htmSearch .= '<input type="text" id="_o_K_o_" value="'. $key .'" class="inp'. (empty($key) ? ' sc' : ' st') .'" onKeyDown="if(event.keyCode==13){__o_SearchPost_o__();return false;}" onfocus="this.style.backgroundPosition=\'0px 52px\';" onblur="if(this.value==\'\')this.style.backgroundPosition=\'0px -48px\';" /><input type="button" id="_o_S_o_" value="搜" onclick="__o_SearchPost_o__();return false;" class="but" title="搜索" />';
			}
			
			$toJS = '<script language="javascript" type="text/JavaScript">function __o_PageJumpPost_o__(){var m='.$this->m_iPageCount.';var p=parseInt(document.getElementById(\'_o_P_o_\').value,10);var n=(isNaN(p)||p==0)?1:(p>m?m:p);window.location.href=\''. $noPsUrl. '\'+n;}</script>';
			$toJS.= '<input type="text" id="_o_P_o_" class="inp" value="'. $this->m_iNowPages .'" size="'. $inputLen .'" maxlength="'. $inputLen .'" style="ime-mode:disabled;" onKeyDown="if(event.keyCode==13){__o_PageJumpPost_o__();return false;}" /><input type="button" id="_o_Z_o_" value="GO" onclick="__o_PageJumpPost_o__();return false;" class="but" title="转到指定页" />';
			
			switch($style)
			{
				//1	文字分页
				case 1: $resHtml = '<table id="os_PAGE_os" border="0" cellspacing="0" cellpadding="0" onselectstart="return false;"><tr><td valign="bottom">'. $htmFirst . $htmLast . $toJS . $htmSearch.'</td></tr></table>';	break;
				//2	总页数 + 文字分页
				case 2: $resHtml = '<table id="os_PAGE_os" border="0" cellspacing="0" cellpadding="0" onselectstart="return false;"><tr><td valign="bottom">'. $htmTotals . $htmFirst . $htmLast . $toJS . $htmSearch.'</td></tr></table>';	break;
				//3	数字分页
				case 3: $resHtml = '<table id="os_PAGE_os" border="0" cellspacing="0" cellpadding="0" onselectstart="return false;"><tr><td valign="bottom">'. $multipage . $toJS . $htmSearch.'</td></tr></table>';	break;
				//4	总页数 + 数字分页
				case 4: $resHtml = '<table id="os_PAGE_os" border="0" cellspacing="0" cellpadding="0" onselectstart="return false;"><tr><td valign="bottom">'. $htmTotals . $multipage . $toJS . $htmSearch.'</td></tr></table>';	break;
				//默认 总页数 + 文字分页 + 数字分页
				default:$resHtml = '<table id="os_PAGE_os" border="0" cellspacing="0" cellpadding="0" onselectstart="return false;"><tr><td valign="bottom">'. $htmTotals . $htmFirst . $htmLast . $multipage . $toJS . $htmSearch.'</td></tr></table>';
			}

			return $resHtml;
		}
		else
		{
			return null;	
		}
	}

	/**
	 * 格式化返回INSERT,UpDate语句
	 * @access 	public
	 * @param 	array 	$aData 数据数组
	 * @param 	string 	$sTableName 表名
	 * @param 	string	$sWhere UPDATE时的条件|null
	 * @throws 	PDOException
	 * @return 	string
	 */
	public function getFormatSql($aData, $sTableName, $sWhere = null)
	{
		if(!is_array($aData) || empty($sTableName))
		{
			throw new PDOException( 'ERROR：参数 $aData 必须是数组, $sTableName不能为空字符串.');
		}
		
		$sql = $field = $value = $upset = '';
		if(is_null( $sWhere))
		{
			foreach($aData as $key=>$val)
			{
				$field .= '`'. $key .'`, ';
				$t = is_string($val) ? "'" : '';
				$value .= $t . (is_null($val)?"null":$val) . $t .', ';
			}
			
			return sprintf('INSERT INTO `%s` (%s) VALUES (%s)', $sTableName, substr($field, 0, -2), substr($value, 0, -2));
		}
		else
		{
			foreach($aData as $key=>$val)
			{
				$t = is_string($val) ? "'" : '';
				$upset .= '`'. $key .'`='. $t . (is_null($val)?"null":$val). $t .', ';
			}
			
			return sprintf('UPDATE `%s` SET %s', $sTableName, substr($upset, 0, -2).(!empty($sWhere) ? ' WHERE '.$sWhere : ''));
		}
	}
}//end class
?>